{"text_format":"1","workbook":"xlsx","n_sheets":4,"sheets":["Schedule","Members","Availability","Instructions"],"not_rendered":{"charts":0,"images":0,"macros":1,"external_links":0,"defined_names":0,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Schedule","index":0,"n_rows":182,"n_cols":17,"rows_kept":182,"cut":false,"hidden":false,"merged":[]}
[null,null,"On call","On call",null,"Comments"]
[]
[null,null,"First contact","Back up contact"]
["2025-01-06","2025-01-12","Resource1","Resource2"]
["2025-01-13","2025-01-19","Resource2","Resource3"]
["2025-01-20","2025-01-26","Resource3","Resource4"]
["2025-01-27","2025-02-02","Resource4","Resource5"]
["2025-02-03","2025-02-09","Resource5","Resource6"]
["2025-02-10","2025-02-16","Resource6","Resource8"]
["2025-02-17","2025-02-23","Resource7","Resource9"]
["2025-02-24","2025-03-02","Resource8","Resource10"]
["2025-03-03","2025-03-09","Resource9","Resource1"]
["2025-03-10","2025-03-16","Resource1","Resource2"]
["2025-03-17","2025-03-23","Resource2","Resource3"]
["2025-03-24","2025-03-30","Resource4","Resource6"]
["2025-03-31","2025-04-06","Resource6","Resource9"]
["2025-04-07","2025-04-13","Resource7","Resource8"]
["2025-04-14","2025-04-20","Resource8","Resource10"]
["2025-04-21","2025-04-27","Resource9","Resource1"]
["2025-04-28","2025-05-04","Resource1","Resource2"]
["2025-05-05","2025-05-11","Resource2","Resource5"]
["2025-05-12","2025-05-18","Resource3","Resource4"]
["2025-05-19","2025-05-25","Resource4","Resource6"]
["2025-05-26","2025-06-01","Resource5","Resource8"]
["2025-06-02","2025-06-08","Resource6","Resource9"]
["2025-06-09","2025-06-15","Resource7","Resource10"]
["2025-06-16","2025-06-22","Resource8","Resource1"]
["2025-06-23","2025-06-29","Resource9","Resource2"]
["2025-06-30","2025-07-06","Resource1","Resource3"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,null,null,null,null,null,null,null,null,null,{"$f":"SUBSTITUTE(C181, CHAR(160),\"\")","v":""},null,{"$f":"CLEAN(L181)","v":""},null,null,{"$f":"TRIM(C181)","v":""}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"TRIM(C182)","v":""}]

{"sheet":"Members","index":1,"n_rows":0,"n_cols":0,"rows_kept":0,"cut":false,"hidden":true,"merged":[]}

{"sheet":"Availability","index":2,"n_rows":200,"n_cols":29,"rows_kept":200,"cut":false,"hidden":false,"merged":[]}
[null,"On call primary Y/N","On call secondary Y/N","2025-01-06","2025-01-13","2025-01-20","2025-01-27","2025-02-03","2025-02-10","2025-02-17","2025-02-24","2025-03-03","2025-03-10","2025-03-17","2025-03-24","2025-03-31","2025-04-07","2025-04-14","2025-04-21","2025-04-28","2025-05-05","2025-05-12","2025-05-19","2025-05-26","2025-06-02","2025-06-09","2025-06-16","2025-06-23","2025-06-30"]
["Resource1","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y"]
["Resource2","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y"]
["Resource3","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","N","Y","N","Y","Y","Y","N","Y","Y","Y","Y","Y","Y","Y","Y"]
["Resource4","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","N","Y","Y","Y","Y","Y","Y","Y","Y"]
["Resource5","Y","Y","Y","N","Y","Y","Y","Y","Y","Y","Y","Y","Y","N","N","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y"]
["Resource6","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y"]
["Resource7","Y","N","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y"]
["Resource8","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","N","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y"]
["Resource9","Y","Y","Y","Y","Y","Y","Y","N","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y"]
["Resource10","N","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
["2023-09-04","2023-09-10","Nitesh"]
["2023-09-11","2023-09-17","Belal"]
["2023-09-18","2023-09-24","Chris"]
["2023-09-25","2023-10-01","Alex"]
["2023-10-02","2023-10-08","NA"]
["2023-10-09","2023-10-15","NA"]
["2023-10-16","2023-10-22","NA"]
["2023-10-23","2023-10-29","NA"]
["2023-10-30","2023-11-05","Nitesh"]

{"sheet":"Instructions","index":3,"n_rows":14,"n_cols":2,"rows_kept":14,"cut":false,"hidden":false,"merged":[]}
[null,"This Macro workbook allows you to generate an oncall rota with a primary and backup contact with weekly frequency, considering any resource holidays"]
[null,"Before using this ensure that macros are enabled"]
[null,"You can use it for daily, two weekly, four weekly or any other suitable frequency also, as long as number of days in a period are fixed"]
[null,"Populate the availability sheet with your team member names and record availability as either Y or N in the time period"]
[null,"Y in column B or C means they are capable of doing the role. Without a Y this column they will not be considered,  this allows you to \"switch off\" any resources easily"]
[null,"A resource will be scheduled for a given time period only when they can do the role and is available for the period"]
[null,"To use for non-week frequencies change the offset in date generation section in module1 code"]
[null,"The module1 code is your template for making any other changes to meet your requirements regarding how many periods you want to generate and your team size etc\nIf you add more lines to Availability ensure you set the maxteam variable in module1 code appropriately\n"]
[null,"If your team size is less than 10 then I would recommend just switching off excess resources by marking them as N in column B and C rather than removing the lines.\nThis means you do not have to change any code"]
[]
[null,"Once Availability sheet is fully populated, click \"Generate schedule\"  button in Schedule sheet to generate the rota"]
[null,"If you make any changes and regenerate then make sure you have deleted the data (row 4 onwards) in Schedule from previous run before regenerating"]
[null,"If the program fails to find resource then it will populate it as NA, you can review it manually"]
[null,"The program has following additional constraints, these are typical industry standards, but you can change them by modifying Module2 code (Getresource function)\n- Selected resource should not have done the same role in previous 3 weeks (or periods)\n- The person who was oncall primary contact in previous week cannot be back up  in current week"]